package com.autoMobile.dao;

import com.autoMobile.bean.ddmUser;
import com.autoMobile.bean.ddmUser1;
import com.autoMobile.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class ddmUserDao {
	//进行数据库原子性操作 CRUD 增删改查
	public ddmUser login(String username, String password) {
		ddmUser user=null;
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		String sql="select username,password,realname from users where username=? and password=?";
		try {
			conn=DBUtil.getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, username);
			pst.setString(2, password);
			rs=pst.executeQuery();
			if(rs.next()) {
				String uname=rs.getString(1);
				String upass=rs.getString(2);
			
				user=new ddmUser();
				user.setUsername(uname);
				user.setPassword(upass);
				
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, pst, rs);
			
		}
		
		return user;
	}
	

	//查找用户信息,本质是查询
	public List<ddmUser> ListUsers(){
		List<ddmUser> list=new ArrayList<ddmUser>();
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		String sql="select * from shenqingbiao";
		try {
			conn=DBUtil.getConnection();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()) {
			    Integer id=rs.getInt(1);
				String uname=rs.getString(2);
				String upass=rs.getString(3);
				String stm=rs.getString(4);
				String otm=rs.getString(5);
				String us=rs.getString(6);
				String mess=rs.getString(7);
				ddmUser user=new ddmUser(id,uname,upass,null,stm,otm,us,mess);
				list.add(user);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, pst, rs);
			
		}
		return list;
	}
	//增加用户信息
	public int addUser(ddmUser user) {
		int n=0;
		String sql="insert into shenqingbiao(username,password,stime,otime,shiyong,message) values(?,?,?,?,?,?)";
		//操作数据库
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		try {
			conn=DBUtil.getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, user.getUsername());
			pst.setString(2, user.getPassword());
			pst.setString(3, user.getStime());
			pst.setString(4, user.getOtime());
			pst.setString(5, user.getShiyong());
			pst.setString(6, user.getMessage());
			pst.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, pst, rs);
			
		}
		return n;
	}
	public int addUser1(ddmUser1 user) {
		int n=0;
		String sql="insert into licheng(soil,omil,zhuang,shuo) values(?,?,?,?)";
		//操作数据库
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		
		try {
			conn=DBUtil.getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, user.getSoil());
			pst.setString(2,user.getOmil());
			pst.setString(3, user.getZhuang());
			pst.setString(4, user.getShuo());
			pst.executeUpdate();
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, pst, rs);
		}
		return n;
	}
	public int deleteUser(int id) {
		int n=0;
		String sql="delete from shenqingbiao where id=?";
		Connection conn = null;
	    PreparedStatement pst = null;
	    try {
	        conn = DBUtil.getConnection();
	        pst = conn.prepareStatement(sql);
	        pst.setInt(1, id); // 设置删除条件为id
	        n = pst.executeUpdate(); // 执行删除操作
	        
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        DBUtil.close(conn, pst, null); // 关闭数据库连接
	    }

		return n;
	}
	
	//修改用户信息，两部分，先获取用户信息，再进行修改
	public ddmUser findUserByID(int id) {
		ddmUser user=null;
		//参考登录代码
		String sql = "SELECT * FROM shenqingbiao WHERE id = ?";
	    Connection conn = null;
	    PreparedStatement pst = null;
	    ResultSet rs = null;

	    try {
	        conn = DBUtil.getConnection();
	        pst = conn.prepareStatement(sql);
	        pst.setInt(1, id); // 设置查询条件
	        rs = pst.executeQuery();

	        if (rs.next()) {
	            user = new ddmUser();
	            user.setId(rs.getInt("id"));
	            user.setUsername(rs.getString("username"));
	            user.setPassword(rs.getString("password"));
	            user.setStime(rs.getString("stime"));
	            user.setOtime(rs.getString("otime"));
	            user.setShiyong(rs.getString("shiyong"));
	            user.setMessage(rs.getString("message"));
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        DBUtil.close(conn, pst, rs); // 关闭数据库连接
	    }
		return user;
	}
	
	public int updateUser(ddmUser user) {
		
		int n=0;
		String sql="update shenqingbiao set username=?,password=?,stime=?,otime=?,shiyong=?,message=? where id=?";
		//操作数据库
				Connection conn=null;
				PreparedStatement pst=null;
				ResultSet rs=null;
				try {
					conn=DBUtil.getConnection();
					pst=conn.prepareStatement(sql);
					pst.setString(1, user.getUsername());
					pst.setString(2, user.getPassword());
					pst.setString(3, user.getStime());
					pst.setString(4, user.getOtime());
					pst.setString(5, user.getShiyong());
					pst.setString(6, user.getMessage());
					pst.setInt(7, user.getId());
					n=pst.executeUpdate();
				}catch(Exception e) {
					e.printStackTrace();
				}finally {
					DBUtil.close(conn, pst, rs);
					
				}
		return n;
	}
}
